/*
Run this script on:

        mssql-ct.db.jwan.it,1433.Caronte2013    -  This database will be modified

to synchronize it with:

        kalgan01.cs.jwan.it,1443.Jambo2014_sviluppo

You are recommended to back up your database before running this script

Script created by SQL Compare version 9.0.0 from Red Gate Software Ltd at 10/04/2014 10:41:18

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[jb_personalizzazioni]'
GO
CREATE TABLE [dbo].[jb_personalizzazioni]
(
[CHIAVE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[VALORE] [varchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NOTE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[INAMOVIBILE] [bit] NOT NULL DEFAULT ((0)),
[MODIFICABILE] [bit] NOT NULL DEFAULT ((0)),
[ORDINE] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_jb_personalizzazioni] on [dbo].[jb_personalizzazioni]'
GO
ALTER TABLE [dbo].[jb_personalizzazioni] ADD CONSTRAINT [PK_jb_personalizzazioni] PRIMARY KEY CLUSTERED  ([CHIAVE])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[situaz_log]'
GO
CREATE TABLE [dbo].[situaz_log]
(
[ID_UPDATE] [int] NOT NULL IDENTITY(1, 1),
[DATA_UPDATE] [datetime] NOT NULL DEFAULT (getdate()),
[CODCORSA] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CATKEY] [int] NOT NULL,
[INIZIALE] [decimal] (18, 2) NOT NULL,
[VENDUTO] [decimal] (18, 2) NOT NULL,
[RISERVATA] [decimal] (18, 2) NOT NULL,
[DISPONIBIL] [decimal] (18, 2) NOT NULL,
[ALLOTM] [decimal] (18, 2) NOT NULL,
[SURICH] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CONTA_SURICH] [int] NOT NULL,
[CODTAR] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DATA_ORA_ULTIMOAGG] [datetime] NOT NULL,
[CAUSALE] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[COD_OP] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[sm_config_JC_JM]'
GO
CREATE TABLE [dbo].[sm_config_JC_JM]
(
[COMPANY_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PROGRAM] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TEST] [bit] NOT NULL DEFAULT ((0)),
[DESCRIZIONE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HOST] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PORT] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PLUGIN] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),
[DBLIST] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ERRMSG_FROM] [int] NOT NULL,
[ERRMSG_TO] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_sm_config_JC_JM] on [dbo].[sm_config_JC_JM]'
GO
ALTER TABLE [dbo].[sm_config_JC_JM] ADD CONSTRAINT [PK_sm_config_JC_JM] PRIMARY KEY CLUSTERED  ([COMPANY_ID], [PROGRAM], [TEST])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[jb_errorMsg]'
GO
CREATE TABLE [dbo].[jb_errorMsg]
(
[DESCRIZIONE] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LINGUA] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CHIAVE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_jb_errorMsg] on [dbo].[jb_errorMsg]'
GO
ALTER TABLE [dbo].[jb_errorMsg] ADD CONSTRAINT [PK_jb_errorMsg] PRIMARY KEY CLUSTERED  ([LINGUA], [CHIAVE])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[jb_helpMessage]'
GO
CREATE TABLE [dbo].[jb_helpMessage]
(
[CHIAVE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LINGUA] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DESCRIZIONE] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_jb_helpMessage] on [dbo].[jb_helpMessage]'
GO
ALTER TABLE [dbo].[jb_helpMessage] ADD CONSTRAINT [PK_jb_helpMessage] PRIMARY KEY CLUSTERED  ([CHIAVE], [LINGUA])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[mapping_itinerari]'
GO
CREATE TABLE [dbo].[mapping_itinerari]
(
[bm] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[json] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK__mapping___3213B11F28C6EE52] on [dbo].[mapping_itinerari]'
GO
ALTER TABLE [dbo].[mapping_itinerari] ADD CONSTRAINT [PK__mapping___3213B11F28C6EE52] PRIMARY KEY CLUSTERED  ([bm])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[mapping_operazioni]'
GO
CREATE TABLE [dbo].[mapping_operazioni]
(
[bm] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[json] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK__mapping___3213B11F2125CC8A] on [dbo].[mapping_operazioni]'
GO
ALTER TABLE [dbo].[mapping_operazioni] ADD CONSTRAINT [PK__mapping___3213B11F2125CC8A] PRIMARY KEY CLUSTERED  ([bm])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[mapping_porti]'
GO
CREATE TABLE [dbo].[mapping_porti]
(
[bm] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[json] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK__mapping___3213B11F24F65D6E] on [dbo].[mapping_porti]'
GO
ALTER TABLE [dbo].[mapping_porti] ADD CONSTRAINT [PK__mapping___3213B11F24F65D6E] PRIMARY KEY CLUSTERED  ([bm])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[sm_config_JC_JM]'
GO
ALTER TABLE [dbo].[sm_config_JC_JM] ADD
CONSTRAINT [FK_sm_config_JC_JM_programmi] FOREIGN KEY ([PROGRAM]) REFERENCES [dbo].[programmi] ([cod_progr])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating extended properties'
GO
EXEC sp_addextendedproperty N'MS_Description', N'ha senso solo per le conf del JC', 'SCHEMA', N'dbo', 'TABLE', N'sm_config_JC_JM', 'COLUMN', N'DBLIST'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_addextendedproperty N'MS_Description', N'ha senso solo per le conf del JM', 'SCHEMA', N'dbo', 'TABLE', N'sm_config_JC_JM', 'COLUMN', N'ERRMSG_FROM'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_addextendedproperty N'MS_Description', N'ha senso solo per le conf del JM', 'SCHEMA', N'dbo', 'TABLE', N'sm_config_JC_JM', 'COLUMN', N'ERRMSG_TO'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_addextendedproperty N'MS_Description', N'ha senso solo per le conf del JC', 'SCHEMA', N'dbo', 'TABLE', N'sm_config_JC_JM', 'COLUMN', N'HOST'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_addextendedproperty N'MS_Description', N'indica il programma a cui si riferiscono le conf. JC|JM', 'SCHEMA', N'dbo', 'TABLE', N'sm_config_JC_JM', 'COLUMN', N'PROGRAM'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering permissions on [dbo].[jb_errorMsg]'
GO
GRANT SELECT ON  [dbo].[jb_errorMsg] TO [JamboClient]
GRANT ALTER ON  [dbo].[jb_errorMsg] TO [JamboGest]
GRANT SELECT ON  [dbo].[jb_errorMsg] TO [JamboGest]
GRANT INSERT ON  [dbo].[jb_errorMsg] TO [JamboGest]
GRANT UPDATE ON  [dbo].[jb_errorMsg] TO [JamboGest]
GO
PRINT N'Altering permissions on [dbo].[jb_helpMessage]'
GO
GRANT SELECT ON  [dbo].[jb_helpMessage] TO [JamboClient]
GRANT SELECT ON  [dbo].[jb_helpMessage] TO [JamboGest]
GRANT INSERT ON  [dbo].[jb_helpMessage] TO [JamboGest]
GRANT DELETE ON  [dbo].[jb_helpMessage] TO [JamboGest]
GRANT UPDATE ON  [dbo].[jb_helpMessage] TO [JamboGest]
GO
PRINT N'Altering permissions on [dbo].[mapping_itinerari]'
GO
GRANT SELECT ON  [dbo].[mapping_itinerari] TO [JamboClient]
GO
PRINT N'Altering permissions on [dbo].[mapping_operazioni]'
GO
GRANT SELECT ON  [dbo].[mapping_operazioni] TO [JamboClient]
GO
PRINT N'Altering permissions on [dbo].[mapping_porti]'
GO
GRANT SELECT ON  [dbo].[mapping_porti] TO [JamboClient]
GO
PRINT N'Altering permissions on [dbo].[situaz_log]'
GO
GRANT SELECT ON  [dbo].[situaz_log] TO [JamboGest]
GO
PRINT N'Altering permissions on [dbo].[sm_config_JC_JM]'
GO
GRANT ALTER ON  [dbo].[sm_config_JC_JM] TO [JamboGest]
GRANT SELECT ON  [dbo].[sm_config_JC_JM] TO [JamboGest]
GRANT INSERT ON  [dbo].[sm_config_JC_JM] TO [JamboGest]
GRANT DELETE ON  [dbo].[sm_config_JC_JM] TO [JamboGest]
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
